<?php
/**
 * Created by PhpStorm.
 * User: DELL
 * Date: 2018/7/5
 * Time: 22:42
 */

namespace app\admin\controller;
use think\Db;
use app\admin\model\Test as TestModel;
use app\admin\model\WechatUser;
use app\home\model\Fenxiao;
use app\home\model\Gate;
use app\home\model\Xuexiao;
class Test extends Admin
{
    /**
     * 测试首页
     */
    public function index(){
        $time = input('time'); // 测试时间
        $code = input('code');  // 试卷标题
        $type = input('type');  // 项目
        $start = strtotime($time);
        $end = strtotime($time)+60*60*24;
        $map = [
            'status' => 0
        ];
        if ($code){
            if ($type){
                $map['code'] = ['eq','***'];
                $types = iconv("utf-8","gbk//TRANSLIT",$type);
                // project_id  数组
                $ids = Gate::where(['class2' => ['like','%'.$types.'%']])->whereOr(['class3' => ['like','%'.$types.'%']])->whereOr(['class4' => ['like','%'.$types.'%']])->column('id');
                if ($ids){
                    $codes = Fenxiao::where(['project_id' => ['in',$ids]])->column('paper_id');
                    if ($codes){
                        $map['code'] = ['in',$codes];
                    }
                }
            }else{
                $co = iconv("utf-8","gbk//TRANSLIT",$code);
                $codes = Fenxiao::where(['title' => ['like','%'.$co.'%']])->column('paper_id');
                if ($codes){
                    $map['code'] = ['in',$codes];
                }else{
                    $map['code'] = ['eq','***'];
                }
            }
        }else{
            if ($type){
                $map['code'] = ['eq','***'];
                $types = iconv("utf-8","gbk//TRANSLIT",$type);
                // project_id  数组
                $ids = Gate::where(['class2' => ['like','%'.$types.'%']])->whereOr(['class3' => ['like','%'.$types.'%']])->whereOr(['class4' => ['like','%'.$types.'%']])->column('id');
                if ($ids){
                    $codes = Fenxiao::where(['project_id' => ['in',$ids]])->column('paper_id');
                    if ($codes){
                        $map['code'] = ['in',$codes];
                    }
                }
            }
        }
        if ($time){
            $map['test_time'] = ['between',[$start,$end]];
        }
        $Code = Db::table('pb_test')->where($map)->group('code')->order('test_time','desc')->column('code');
        $list = Db::table('pb_test')
            ->group('code')
            ->where($map)
            ->order('test_time','desc')
            ->paginate(10,false,['query' => ['time'=> $time,'code' => $code,'type' => $type]],count($Code));
        $this->assign('list',$list);
        return $this->fetch();
    }
    /**
     * 排名
     */
    public function rank(){
        $code = input('code');
        $name = input('name');
        if ($name){
            $dx = iconv("UTF-8", "GBK//TRANSLIT", $name);
            $School = Xuexiao::where(['dx' => $dx])->value('dx_id');
            $school_id = $School ? $School : '*';
            $map = ['a.review_status' => 1,'a.role' => 0,'a.school_id' => $school_id,'b.code' => $code,'b.status' => 0];
        }else{
            $map = ['a.review_status' => 1,'a.role' => 0,'b.code' => $code,'b.status' => 0];
        }
        $list = Db::table('pb_test')
            ->alias('b')
            ->join('pb_wechat_user a',"a.openid = b.openid")
            ->field('b.id,a.name,a.school_id,a.order_id,b.score')
            ->where($map)
            ->order('b.score','desc')
            ->select();
        $x = 0;
        $y = 0;
        foreach ($list as $value){
            $y += $value['score'];
            $x++;
        }
        if ($y == 0){
            $sum = 0;
        }else{
            $sum = round($y/$x,1);
        }
        $this->assign('list',$list);
        $this->assign('code',$code);
        $this->assign('sum',$sum);
        return $this->fetch();
    }
    /**
     * 导出 Excel
     */
    public function outpaper(){
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        $code = input('code');
        $time = input('time');
        //引用PHPExcel
        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        //给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
        $cellNames = ['A','B','C','D','E'];
        $titles = ['排名','姓名','所属学校','VIP账号','分数']; //对应Excel标题
        //标题输出
        foreach ($titles as $k => $v) {
            $PHPSheet->setCellValue($cellNames[$k].'5',$v);
            $PHPSheet->getStyle($cellNames[$k].'5')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellNames[$k])->setWidth(20);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellNames[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        $list = TestModel::where(['code' => $code,'status' => 0])->order('score','desc')->field('openid,score')->select();
        $x = 0; // 总分
        $y = 0; // 总人数
        $i = 0;
        foreach ($list as $key => $value){
            $key = $key + 6;
            $User = WechatUser::where(['openid' => $value['openid']])->field('name,school_id,headimgurl,order_id,review_status,unsubscribe_time,subscribe')->find();
            if ($User['review_status'] == 0 || !empty($User['unsubscribe_time']) || $User['subscribe'] == 0){
               // 没有数据 不操作
                $i+= 1;
            }else{
                $x += $value['score'];
                $y++;
                $w = $key-$i;
                $PHPSheet->setCellValue($cellNames[0] . $w, $w-5)
                    ->setCellValue($cellNames[1] . $w, $User['name']?$User['name']:"暂无")
                    ->setCellValue($cellNames[2] . $w, get_school($User['school_id']))
                    ->setCellValue($cellNames[3] . $w, $User['order_id'])
                    ->setCellValue($cellNames[4] . $w, $value['score']);
            }
        }
        if ($y == 0){
            $sum = 0;
        }else{
            $sum = round($x/$y,1);
        }

        $cellName = ['A','B','C','D','E','F','G'];
        $title = ['阶段','大类','项目大类','一级项目','二级项目','测试时间','平均成绩']; //对应Excel标题
        //标题输出
        foreach ($title as $k => $v) {
            $PHPSheet->setCellValue($cellName[$k].'1',$v);
            $PHPSheet->getStyle($cellName[$k].'1')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellName[$k])->setWidth(20);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellName[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        $PHPSheet->setCellValue($cellName[0] . 2, get_stage($code))
            ->setCellValue($cellName[1] . 2, get_class1($code))
            ->setCellValue($cellName[2] . 2, get_class2($code))
            ->setCellValue($cellName[3] . 2, get_class3($code))
            ->setCellValue($cellName[4] . 2, get_class4($code))
            ->setCellValue($cellName[5] . 2, date('Y-m-d H:i',$time))
            ->setCellValue($cellName[6] . 2, $sum);
        //内容输出
        $fileName = date('Y-m-d',$time)."测试排名";
        // 设置下载头部
        $ua = $_SERVER["HTTP_USER_AGENT"];
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\''.$fileName.'.xls');
        } else {
            header('Content-Disposition: attachment; filename="'.$fileName.'.xls');
        }
        header('Content-Type: application/vnd.ms-excel;');
        header('Cache-Control: max-age=0');
        // 输出下载文件
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    /**
     * 删除
     */
    public function del(){
        $id = input('id');
        if (empty($id)){
            return $this->error('系统参数错误');
        }
        $Test = new TestModel();
        $res = $Test->where('id',$id)->delete();
        if ($res){
            return $this->success('删除成功');
        }else{
            return $this->error('删除失败');
        }
    }
    /**
     * 添加
     */
    public function edit(){
        $Test = new TestModel();
        if (IS_POST){
            $data = input('post.');
            if ($data['test_time']){
                $data['test_time'] = strtotime($data['test_time']);
            }
            $data['create_time'] = time();
            $type = $data['type'];
            unset($data['type']);
            if ($type == 1){
                // 更新 覆盖
                $res = $Test->where(['code' => $data['code'],'status' => 0,'openid' => $data['openid']])->update(['test_time' => $data['test_time'],'score' => $data['score'],'create_time' => $data['create_time']]);
            }else{
                // 添加
                $res = $Test->save($data);
            }
            if ($res){
                return $this->success('操作成功',Url('Test/rank?code='.$data['code']));
            }else{
                return $this->error('操作失败');
            }
        }else{
            $years = date('Y');
            $code = input('code');
            $res = TestModel::where(['code' => $code,'status' => 0])->field('openid')->find();
            if (empty($res)){
                return $this->error('测试编码错误,请重试');
            }
            $Year = [];
            while (count($Year) <= 5){
                $Year[] = $years;
                $years += 1;
            }
            $this->assign('years',$Year);
            $this->assign('year',date('Y'));
            $this->assign('code',$code);
            return $this->fetch();
        }
    }
    /**
     * 筛选
     */
    public function sel(){
        $name = input('name');
        $users = WechatUser::where(['review_status' => 1,'role' => 0,'name'=>['like','%'.$name.'%']])->field('name,openid,order_id,school_id')->select();
        foreach ($users as $value){
            $value['school_id'] = get_school($value['school_id']);
        }
        return $this->success('加载成功','',$users);
    }
    /**
     * 查看是否有学员
     */
    public function play(){
        $Test = new TestModel();
        $code = input('code');
        $openid = input('openid');
        $res = $Test->where(['code' => $code,'status' => 0,'openid' => $openid])->field('score')->find();
        if ($res){
            // 已有成绩
            return ['code' => 1];
        }else{
            // 没有
            return ['code' => 0];
        }
    }
}